﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Xml;

namespace DeVierKoeden.Dynamicweb.CreateSite.Rizzo
{
  /// <summary>
  /// Helper form to customize item types and fields.
  /// </summary>
  public partial class ConfigureRizzoForm : Form
  {
    private readonly string[] _designNameFolders = { "RizzoDesignFolder" };
    internal string CustomZipFileOrFolder { get; set; }
    private InstallSettings InstallSettings { get; set; }

    /// <summary>
    /// Creates a new instance of the ConfigureRizzoForm.
    /// </summary>
    public ConfigureRizzoForm()
    {
      InitializeComponent();
    }

    /// <summary>
    /// Configures the dialog with the appropriate settings.
    /// </summary>
    public void Initialize()
    {
      AddNodesFromFolder();
    }

    private void AddNodesFromFolder()
    {
      treeView1.ImageList = imageList1;
      using (var archive = ZipFile.OpenRead(CustomZipFileOrFolder))
      {
        var itemTypes = archive.Entries.Where(x => (x.FullName.ToLower().Contains("files\\system\\items") || x.FullName.ToLower().Contains("files/system/items")) && x.FullName.EndsWith(".xml"));
        foreach (var itemType in itemTypes)
        {
          var stream = itemType.Open();
          var sr = new StreamReader(stream);
          var xml = sr.ReadToEnd();

          string fileName = Path.GetFileName(itemType.FullName);
          AddNodesFromFile(xml, fileName);
        }
      }

      treeView1.AfterCheck += HandleCheck;
    }

    private void AddNodesFromFile(string itemXml, string fileName)
    {
      var doc = new XmlDocument();
      doc.LoadXml(itemXml);

      treeView1.CheckBoxes = true;

      var itemNode = doc.SelectSingleNode("/items/item");
      if (itemNode?.Attributes != null)
      {
        AddItemRootNode(fileName, doc, itemNode);
      }
      if (treeView1.Nodes.Count > 7)
      {
        Height = 600;
      }
    }

    private void AddItemRootNode(string fileName, XmlDocument doc, XmlNode itemXmlNode)
    {
      if (itemXmlNode.Attributes == null)
      {
        throw new ArgumentNullException(nameof(itemXmlNode));
      }
      var itemTreeNode = new ItemTreeNode { Text = itemXmlNode.Attributes["name"].InnerText, SystemName = itemXmlNode.Attributes["systemName"].InnerText, Name = fileName, Inherits = itemXmlNode.Attributes["inherits"].InnerText };
      treeView1.Nodes.Add(itemTreeNode);
      itemTreeNode.Checked = true;

      AddGroups(doc, itemTreeNode);
      itemTreeNode.ExpandAll();
      AddFields(itemXmlNode, itemTreeNode);
    }

    private static void AddGroups(XmlNode doc, ItemTreeNode itemTreeNode)
    {
      var xmlGroups = doc.SelectNodes("/items/item/layout/groups/group");
      if (xmlGroups != null && xmlGroups.Count > 0)
      {
        foreach (XmlNode xmlGroup in xmlGroups)
        {
          if (xmlGroup.Attributes != null)
          {
            var groupNode = new GroupTreeNode { Text = xmlGroup.Attributes["name"].InnerText, Name = xmlGroup.Attributes["systemName"].InnerText };
            itemTreeNode.Nodes.Add(groupNode);
            groupNode.Checked = true;

            var selectedField = xmlGroup.SelectNodes("fields/field");
            if (selectedField != null)
            {
              foreach (XmlNode xmlNode in selectedField)
              {
                if (xmlNode.Attributes != null)
                {
                  groupNode.SelectedFields.Add(xmlNode.Attributes["systemName"].InnerText);
                }
              }
            }
            groupNode.Collapse();
            groupNode.Checked = true;
          }
        }
      }
    }

    private void AddFields(XmlNode itemXmlNode, ItemTreeNode itemTreeNode)
    {
      var fields = itemXmlNode.SelectNodes("fields/field");
      if (fields != null && fields.Count > 0)
      {
        foreach (XmlNode field in fields)
        {
          TreeNode parentNode = FindMatchingGroupForField(itemTreeNode, field);
          if (parentNode == null)
          {
            parentNode = itemTreeNode;
          }
          if (field.Attributes != null)
          {
            var fieldNode = new FieldTreeNode { Text = field.Attributes["name"].InnerText + BuildFieldType(field), Name = field.Attributes["systemName"].InnerText };
            parentNode.Nodes.Add(fieldNode);
            fieldNode.Checked = true;
          }
        }
      }
    }

    private TreeNode FindMatchingGroupForField(ItemTreeNode itemXmlNode, XmlNode field)
    {
      foreach (var groupNode in itemXmlNode.GetGroups())
      {
        if (field.Attributes != null && groupNode.SelectedFields.Contains(field.Attributes["systemName"].InnerText))
        {
          return groupNode;
        }
      }
      return null;
    }

    private string BuildFieldType(XmlNode field)
    {
      var selectSingleNode = field.SelectSingleNode("editor");
      if (selectSingleNode?.Attributes != null)
      {
        string type = selectSingleNode.Attributes["type"].InnerText;
        string fieldType = "";
        switch (type)
        {
          case "Dynamicweb.Content.Items.Editors.IntegerEditor, Dynamicweb":
            fieldType = "Int";
            break;
          case "Dynamicweb.Content.Items.Editors.FileEditor, Dynamicweb":
            fieldType = "File";
            break;
          case "Dynamicweb.Content.Items.Editors.LinkEditor, Dynamicweb":
            fieldType = "Link";
            break;
          case "Dynamicweb.Content.Items.Editors.TextEditor, Dynamicweb":
            fieldType = "Text";
            break;
          case "Dynamicweb.Content.Items.Editors.ItemListEditor, Dynamicweb":
            fieldType = "List";
            break;
          case "Dynamicweb.Content.Items.Editors.CheckboxEditor, Dynamicweb":
            fieldType = "Checkbox";
            break;
          case "Dynamicweb.Content.Items.Editors.RichTextEditor, Dynamicweb":
            fieldType = "Rich Text";
            break;
          case "Dynamicweb.Content.Items.Editors.LongTextEditor, Dynamicweb":
            fieldType = "Long Text";
            break;
          case "Dynamicweb.Content.Items.Editors.RadioButtonListEditor`1, Dynamicweb":
            fieldType = "Radio Button List";
            break;
          case "Dynamicweb.Content.Items.Editors.CheckboxListEditor`1, Dynamicweb":
            fieldType = "Check Box List";
            break;
          case "Dynamicweb.Content.Items.Editors.DropDownListEditor`1, Dynamicweb":
          case "Dynamicweb.Content.Items.Editors.DropDownListEditor, Dynamicweb":
            fieldType = "Drop Down List";
            break;
          case "Dynamicweb.Content.Items.Editors.DateEditor, Dynamicweb":
            fieldType = "Date";
            break;
          case "Dynamicweb.Content.Items.Editors.DateTimeEditor, Dynamicweb":
            fieldType = "Date/time";
            break;
          case "Dynamicweb.Content.Items.Editors.FolderEditor, Dynamicweb":
            fieldType = "Folder";
            break;
          case "Dynamicweb.Content.Items.Editors.ColorEditor, Dynamicweb":
            fieldType = "Color";
            break;
          case "Dynamicweb.Content.Items.Editors.ProductEditor, Dynamicweb":
            fieldType = "Product";
            break;
          case "Dynamicweb.Content.Items.Editors.UserEditor, Dynamicweb":
            fieldType = "User";
            break;
          case "Dynamicweb.Content.Items.Editors.HiddenFieldEditor, Dynamicweb":
            fieldType = "Hidden field";
            break;
          case "Dynamicweb.Content.Items.Editors.GeolocationEditor, Dynamicweb":
            fieldType = "Geo location";
            break;
          case "Dynamicweb.Ecommerce.Content.Items.Editors.ProductEditor, Dynamicweb.Ecommerce": // DW 9 type
            fieldType = "Product editor";
            break;
          default:
            MessageBox.Show($"TODO. Add support for {type}");
            break;
        }

        if (!string.IsNullOrEmpty(fieldType))
        {
          return $" ({fieldType})";
        }
      }
      return "";
    }

    private void HandleCheck(object sender, TreeViewEventArgs e)
    {
      treeView1.AfterCheck -= HandleCheck;
      if (e.Node is ItemTreeNode)
      {
        SyncDown(e);
      }
      else
      {
        if (e.Node is GroupTreeNode)
        {
          SyncUp(e);
          SyncDown(e);
        }
        else
        {
          SyncUp(e);
        }
      }

      treeView1.AfterCheck += HandleCheck;
      var node = e.Node as ItemTreeNode;
      if (node != null)
      {
        if (node.Checked)
        {
          SelectParentTypes(node);
        }
        else
        {
          DeselectChildTypes(node);
        }
      }
    }

    private void SelectParentTypes(ItemTreeNode node)
    {
      var nodes = treeView1.Nodes.OfType<ItemTreeNode>().Where(x => x.SystemName == node.Inherits).ToList();
      nodes.ForEach(x => x.Checked = true);
    }

    private void DeselectChildTypes(ItemTreeNode node)
    {
      var nodes = treeView1.Nodes.OfType<ItemTreeNode>().Where(x => x.Inherits == node.SystemName).ToList();
      nodes.ForEach(x => x.Checked = false);
    }

    private static void SyncDown(TreeViewEventArgs e)
    {
      foreach (TreeNode item in e.Node.Nodes)
      {
        item.Checked = e.Node.Checked;
        var itemNode = item as ItemTreeNode;
        if (itemNode != null)
        {
          foreach (var categoryNode in itemNode.GetGroups())
          {
            categoryNode.Checked = e.Node.Checked;
            foreach (var fieldNode in categoryNode.GetFields())
            {
              fieldNode.Checked = e.Node.Checked;
            }
          }
        }
        var categoryNode2 = item as GroupTreeNode;
        if (categoryNode2 != null)
        {
          categoryNode2.Checked = e.Node.Checked;
          foreach (var fieldNode in categoryNode2.GetFields())
          {
            fieldNode.Checked = e.Node.Checked;
          }
        }
      }
    }

    private static void SyncUp(TreeViewEventArgs e)
    {
      var nodes = new List<TreeNode>();
      foreach (TreeNode item in e.Node.Parent.Nodes)
      {
        nodes.Add(item);
      }
      e.Node.Parent.Checked = nodes.Any(x => x.Checked);
      if (e.Node is FieldTreeNode)
      {
        foreach (TreeNode item in e.Node.Parent.Parent.Nodes)
        {
          nodes.Add(item);
        }
        e.Node.Parent.Parent.Checked = nodes.Any(x => x.Checked);
      }
    }

    private void DeleteItemType(TreeNode node)
    {
      string fileName = Path.Combine(InstallSettings.TargetFilesFolder + "\\System\\Items", node.Name);
      File.Delete(fileName);

      string tableName = new Regex(".xml", RegexOptions.IgnoreCase).Replace(node.Name, "");
      string deleteTable = string.Format(@"-S{1} -E -d""{0}"" -Q""DROP TABLE [dbo].[{2}]""", InstallSettings.DatabaseName, InstallSettings.SqlServerInstance, tableName);
      Helpers.RunProcess("SQLCMD", deleteTable);
    }
    internal void SaveItemTypeFiles(InstallSettings installSettings)
    {
      InstallSettings = installSettings;
      foreach (TreeNode node in treeView1.Nodes)
      {
        if (node.Checked && node is ItemTreeNode)
        {
          SaveItemTypeFile((ItemTreeNode)node);
        }
        else
        {
          DeleteItemType(node);
        }
      }
    }

    internal void FixupDesignName(InstallSettings installSettings)
    {
      if (!string.IsNullOrWhiteSpace(DesignName.Text))
      {
        RenameDesignFolders(installSettings);
        RenameDesignFolderInFiles(installSettings);
        RenameDesignInDatabase(installSettings);
        RegisterDesignInDatabase(installSettings);
      }
    }

    private void RenameDesignInDatabase(InstallSettings installSettings)
    {
      const string sql = @"DELETE FROM ItemType_Slideshow WHERE Id IN (1,2)
        UPDATE ItemType_Slideshow SET [Image] = Replace([Image], 'RizzoDesignFolder' , '{0}')
        UPDATE ItemType_StandardParagraph SET [Image] = Replace([Image], 'RizzoDesignFolder' , '{0}')

        UPDATE ItemType_StandardParagraph SET [Image] = '/Files/Images/{0}/example.jpg' WHERE [Image] = '/Files/Files/Images/example.jpg'

        UPDATE ItemType_Email_Marketing_Paragraph SET [Image] = REPLACE([Image], '/Files/Files/Images/', '/Files/Images/{0}/')
        UPDATE ItemType_MediaGallery SET [Image] = REPLACE([Image], '/Files/Images/RizzoDesignFolder/', '/Files/Images/{0}/')
        UPDATE ItemType_ProductDownloads SET [File] = REPLACE([File], '/Files/Files/DesignFolder/', '/Files/{0}/')
";
      ExecuteSqlAgainstSqlConnection(installSettings, string.Format(sql, DesignName.Text));
    }

    internal void DeleteDynamicwebOrdersIfChecked(InstallSettings installSettings)
    {
      if (clearOrderHistory.Checked)
      {
        DeleteDynamicwebOrders(installSettings);
      }
    }

    private void DeleteDynamicwebOrders(InstallSettings installSettings)
    {
      if (clearOrderHistory.Checked)
      {
        string sql = @"DELETE FROM EcomRmaComments
                  DELETE FROM EcomLoyaltyUserTransaction
                  DELETE FROM EcomRecurringOrder
                  DELETE FROM EcomRmaOrderLines
                  DELETE FROM EcomRmas
                  DELETE FROM EcomOrderLines
                  DELETE FROM EcomOrders
                  DELETE FROM EcomOrderDebuggingInfo
                  DELETE FROM EcomSavedForLater
                  UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'ORDER'
                  UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'OL'";
        sql = sql.Replace(Environment.NewLine, ";"); // Convert multi line to semi colon separated sql statements needed for SqlCmd
        string process = string.Format(@"-S{0} -E -d""{1}"" -Q""{2}""", installSettings.SqlServerInstance, installSettings.DatabaseName, sql);
        Helpers.RunProcess("SQLCMD", process);
      }
    }

    internal void DeleteModuleDataIfChecked(InstallSettings installSettings)
    {
      if (clearModuleData.Checked)
      {
        DeleteModuleData(installSettings);
      }
    }

    private void DeleteModuleData(InstallSettings installSettings)
    {
      string sql = @"DELETE FROM FormSubmitData
        DELETE FROM FormSubmit
        DELETE FROM RecycleBin
        DELETE FROM TrashBin
        DELETE FROM FormOptions WHERE FormOptionsFormFieldID IN (SELECT FormFieldID FROM FormField WHERE FormFieldFormID NOT IN (1,2,3,5))
        DELETE FROM FormFieldOption WHERE FormFieldOptionFieldID IN (SELECT FormFieldID FROM FormField WHERE FormFieldFormID NOT IN (1,2,3,5))
        DELETE FROM FormField WHERE FormFieldFormID NOT IN (1,2,3,5)
        DELETE FROM Form WHERE FormID NOT IN (1,2,3,5)
        DELETE FROM DMViewSetting
        DELETE FROM DMFormSetting";

      sql = sql.Replace(Environment.NewLine, ";"); // Convert multi line to semi colon separated sql statements needed for SqlCmd
      string process = string.Format(@"-S{0} -E -d""{1}"" -Q""{2}""", installSettings.SqlServerInstance, installSettings.DatabaseName, sql);
      Helpers.RunProcess("SQLCMD", process);
    }

    internal void CleanInstallIfChecked(InstallSettings installSettings)
    {
      if (rdCleanInstall.Checked)
      {
        DeleteDynamicwebOrders(installSettings);
        DeleteUnneededFolders(installSettings);
        DeleteStats(installSettings);
        DeleteModuleData(installSettings);
        DeleteEmailMarketingData(installSettings);
        DeleteFromOldTables(installSettings);
        DeleteEcomData(installSettings);
        DeleteUserData(installSettings);
        UpdateOtherData(installSettings);
      }
      UpdateIndexes(installSettings);
    }

    private void UpdateOtherData(InstallSettings installSettings)
    {
      const string sql = "UPDATE EcomShops SET ShopCreated = GetDate()";
      ExecuteSqlAgainstSqlConnection(installSettings, sql);
    }

    private void DeleteUserData(InstallSettings installSettings)
    {
      if (rdCleanInstall.Checked)
      {
        const string sql = @"DELETE FROM AccessUserCard
                      DELETE FROM AccessUserPassword
                      DELETE FROM AccessUserAddress
                      DELETE FROM AccessUser WHERE (AccessUserType = 5) AND (AccessUserGroups NOT LIKE '@283@') 
                      DELETE FROM EcomCustomerFavoriteLists
                      DELETE FROM EcomCustomerFavoriteProducts
                   ";
        ExecuteSqlAgainstSqlConnection(installSettings, sql);
      }
    }

    private void DeleteFromOldTables(InstallSettings installSettings)
    {
      const string sql = @"DELETE FROM TemplateMenu
DELETE FROM Template
DELETE FROM StylesheetNodeclass
DELETE FROM StylesheetStylesheet
DELETE FROM StylesheetClass
DELETE FROM TemplateMenuType            
DELETE FROM NewsLetterV3Settings";
      ExecuteSqlAgainstSqlConnection(installSettings, sql);
    }

    private void UpdateIndexes(InstallSettings installSettings)
    {
      const string sql = @"DECLARE @TableName varchar(255) 
              DECLARE TableCursor CURSOR FOR 
              SELECT table_name FROM information_schema.tables 
              WHERE table_type = 'base table' 
 
              OPEN TableCursor 
 
              FETCH NEXT FROM TableCursor INTO @TableName 
              WHILE @@FETCH_STATUS = 0 
              BEGIN 
                DBCC DBREINDEX(@TableName,' ',90) 
                FETCH NEXT FROM TableCursor INTO @TableName 
              END 
              CLOSE TableCursor 
              DEALLOCATE TableCursor";

      ExecuteSqlAgainstSqlConnection(installSettings, sql);
    }

    private static void ExecuteSqlAgainstSqlConnection(InstallSettings installSettings, string sql)
    {
      using (var connection = new SqlConnection(installSettings.BuildConnectionString()))
      {
        connection.Open();
        using (var command = new SqlCommand(sql, connection))
        {
          command.ExecuteNonQuery();
        }
      }
    }

    private void DeleteEcomData(InstallSettings installSettings)
    {
      if (rdCleanInstall.Checked)
      {
        const string sql = @" DELETE FROM EcomProducts WHERE ProductID NOT IN ('PROD1', 'PROD34', 'PROD40', 'PROD41', 'PROD42')
                      DELETE FROM EcomGroupProductRelation WHERE GroupProductRelationProductID NOT IN ('PROD1', 'PROD34', 'PROD40', 'PROD41', 'PROD42')
                      DELETE FROM EcomVariantgroupProductRelation WHERE VariantgroupProductRelationProductID NOT IN ('PROD1', 'PROD34', 'PROD40', 'PROD41', 'PROD42')
                      DELETE FROM EcomGroups WHERE GroupID NOT in ('GROUP1')
                      DELETE FROM EcomGroupRelations
                      DELETE FROM EcomShopGroupRelation WHERE ShopGroupGroupID NOT in ('GROUP1')
                      DELETE FROM EcomVariantOptionsProductRelation WHERE VariantOptionsProductRelationProductID NOT IN ('PROD1', 'PROD34', 'PROD40', 'PROD41', 'PROD42')
                      DELETE FROM EcomOrderLines
                      DELETE FROM EcomOrders
                      DELETE FROM EcomProductCategoryFieldTranslation
                      DELETE FROM EcomProductCategoryTranslation
                      DELETE FROM EcomProductCategoryFieldValue
                      DELETE FROM EcomProductCategoryField
                      DELETE FROM EcomProductCategory
                      DELETE FROM EcomDetails  WHERE DetailProductID NOT IN ('PROD1', 'PROD34', 'PROD40', 'PROD41', 'PROD42')
                      DELETE FROM EcomVouchers
                      DELETE FROM EcomVoucherLists
                      DELETE FROM [dbo].[EcomLoyaltyReward]
                      DELETE FROM [dbo].[EcomLoyaltyRewardRule]
                      DELETE FROM [dbo].[EcomLoyaltyRewardTranslation]
                      DELETE FROM [dbo].[EcomLoyaltyUserTransaction]

DELETE FROM [dbo].[EcomDiscountTranslation]
DELETE FROM [dbo].[EcomDiscount]
DELETE FROM [dbo].[EcomSalesDiscountLanguages]
DELETE FROM [dbo].[EcomSalesDiscountCurrencies]
DELETE FROM [dbo].[EcomSalesDiscount]



                      DELETE FROM OMCProfileDynamics

                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'VARGRP'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'VO'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'VARGRPPRODREL'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'CART'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'ORDER'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'OL'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 44 WHERE NumberType = 'PROD'
                      UPDATE dbo.EcomNumbers SET NumberCounter = 1 WHERE NumberType = 'GROUP'";

        ExecuteSqlAgainstSqlConnection(installSettings, sql);
      }
    }

    internal void DeleteStatsIfChecked(InstallSettings installSettings)
    {
      if (clearStats.Checked)
      {
        DeleteStats(installSettings);
      }
    }

    private void DeleteStats(InstallSettings installSettings)
    {
      const string sql = @"    DELETE FROM Statv2Session
            DELETE FROM Statv2Page
            DELETE FROM Statv2Object
            DELETE FROM Statv2SessionBot
            DELETE FROM Statv2UserAgents
            DELETE FROM Statv2NotFound
            DELETE FROM StatExtranet
            DELETE FROM ActionLog
            DELETE FROM GeneralLog
            DELETE FROM EcomOrderDebuggingInfo
            DELETE FROM NonBrowserSession
            DELETE FROM EmailAction
            DELETE FROM VersionData
            ";
      ExecuteSqlAgainstSqlConnection(installSettings, sql);
    }

    private void DeleteEmailMarketingData(InstallSettings installSettings)
    {
      const string sql = @"DECLARE @daysToKeepNewsletters INT = 0
          DECLARE @deleteBatchSize INT = 100000
          PRINT 'Creating temp tables'
          CREATE TABLE #EmailsToDelete(EmailId INT, EmailMessageId int)
          CREATE TABLE #LinksToDelete(LinkId INT)
          CREATE TABLE #RecipientsToDelete(RecipientId INT)
          PRINT 'Finding emails to delete'
          INSERT INTO #EmailsToDelete (EmailId, EmailMessageId) 
          SELECT EMailId, EmailMessageId FROM EmailMarketingEmail WHERE EmailCreatedDate < DATEADD(DAY, -@daysToKeepNewsletters, GETDATE())
          PRINT 'Deleting engagement index and split tests'
          DELETE FROM EmailMarketingEngagementIndex WHERE EngagementIndexEmailId IN (SELECT EmailId FROM #EmailsToDelete)
          DELETE FROM EmailMarketingSplitTest WHERE SplitTestEmailId IN (SELECT EmailId FROM #EmailsToDelete)
          PRINT 'Deleting message tags'
          DELETE FROM EmailMessageTag WHERE MessageTagMessageId IN (SELECT EmailMessageId FROM #EmailsToDelete)
          PRINT 'Finding links to delete'
          INSERT INTO #LinksToDelete (LinkId)
          SELECT LinkId FROM OMCLink WHERE (LinkReferenceType = N'EmailMessaging') AND LinkReferenceKey IN (SELECT EmailMessageId FROM #EmailsToDelete)
          PRINT 'Deleting link clicks'
          DECLARE @r INT;
          SET @r = 1;
          WHILE @r > 0
          BEGIN
            BEGIN TRANSACTION;
            DELETE TOP (@deleteBatchSize) FROM dbo.OMCLinkClick WHERE LinkClickLinkId IN (SELECT LinkId FROM #LinksToDelete)
            SET @r = @@ROWCOUNT;
            COMMIT TRANSACTION;
            CHECKPOINT; 
          END
          PRINT 'Deleting links'
          SET @r = 1;
          WHILE @r > 0
          BEGIN
            BEGIN TRANSACTION;
            DELETE TOP (@deleteBatchSize) FROM dbo.OMCLink WHERE LinkId IN (SELECT LinkId FROM #LinksToDelete)
            SET @r = @@ROWCOUNT;
            COMMIT TRANSACTION;
            CHECKPOINT;    
          END
          PRINT 'Finding recipients'
          INSERT INTO #RecipientsToDelete ( RecipientId )
          SELECT RecipientId FROM dbo.EmailRecipient WHERE RecipientMessageId IN (SELECT EmailMessageId FROM #EmailsToDelete)
          PRINT 'Deleting recipient tags'
          SET @r = 1;
          WHILE @r > 0
          BEGIN
            BEGIN TRANSACTION;
          DELETE TOP (@deleteBatchSize) FROM EmailRecipientTag WHERE RecipientTagRecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
            SET @r = @@ROWCOUNT;
            COMMIT TRANSACTION;
            CHECKPOINT;
          END
          PRINT 'Deleting recipients'
          SET @r = 1;
          WHILE @r > 0
          BEGIN
            BEGIN TRANSACTION;
          DELETE TOP (@deleteBatchSize) FROM EmailRecipient WHERE RecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
            SET @r = @@ROWCOUNT;
            COMMIT TRANSACTION;
            CHECKPOINT; 
          END
          PRINT 'Deleting email actions'
          SET @r = 1;
          WHILE @r > 0
          BEGIN
            BEGIN TRANSACTION;
          DELETE TOP (@deleteBatchSize) FROM EmailAction WHERE ActionRecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
            SET @r = @@ROWCOUNT;
            COMMIT TRANSACTION;
            CHECKPOINT;  
          END
          PRINT 'Deleting emails'
          DELETE FROM EmailMarketingEmail WHERE EmailId IN (SELECT EmailId FROM #EmailsToDelete)
          PRINT 'Dropping temp tables'
          DROP TABLE #EmailsToDelete
          DROP TABLE #LinksToDelete
          DROP TABLE #RecipientsToDelete

          DELETE FROM [EmailAction]
          DELETE FROM [EmailMarketingEmail]
          DELETE FROM [EmailMarketingEngagementIndex]
          DELETE FROM [EmailMarketingFolder]
          DELETE FROM [EmailMarketingSplitTest]
          DELETE FROM [EmailMarketingTopFolder]
          DELETE FROM [EmailMessageTag]
          DELETE FROM [EmailRecipient]
          DELETE FROM [EmailMessage]";

      ExecuteSqlAgainstSqlConnection(installSettings, sql);
    }
    
    internal void ShrinkDatabase(InstallSettings installSettings)
    {
      string sql = string.Format(@"DBCC SHRINKDATABASE(N'{0}' )", installSettings.DatabaseName);
      string process = string.Format(@"-S{0} -E -d""{1}"" -Q""{2}""", installSettings.SqlServerInstance, installSettings.DatabaseName, sql);
      Helpers.RunProcess("SQLCMD", process);
    }

    internal void DeleteUnneededFoldersIfChecked(InstallSettings installSettings)
    {
      if (cleanFolders.Checked)
      {
        DeleteUnneededFolders(installSettings);
      }
      OptionalDeleteFile(string.Format("{0}\\UpdateLog.log", installSettings.TargetFilesFolder));
    }

    private void DeleteUnneededFolders(InstallSettings installSettings)
    {
      if (cleanFolders.Checked)
      {
        OptionalDelete(string.Format("{0}\\Templates\\Designs\\{1}\\_parsed", installSettings.TargetFilesFolder, DesignName.Text));
        OptionalDelete(string.Format("{0}\\Files\\MailLog", installSettings.TargetFilesFolder));
        OptionalDelete(string.Format("{0}\\System\\Log", installSettings.TargetFilesFolder));
        OptionalDelete(string.Format("{0}\\System\\Diagnostics", installSettings.TargetFilesFolder));
        OptionalDelete(string.Format("{0}\\System\\_search", installSettings.TargetFilesFolder));
        OptionalDelete(string.Format("{0}\\cache.net", installSettings.TargetFilesFolder));
        OptionalDelete(string.Format("{0}\\Papirkurv", installSettings.TargetFilesFolder));
      }
    }

    private void OptionalDeleteFile(string file)
    {
      try
      {
        if (File.Exists(file))
        {
          File.Delete(file);
        }
      }
      catch
      {
        // Ignore; not a big deal if deleting those didn't succeed.
      }
    }


    private void OptionalDelete(string folder)
    {
      try
      {
        if (Directory.Exists(folder))
        {
          Directory.Delete(folder, true);
        }
      }
      catch
      {
        // Ignore; not a big deal if deleting those didn't succeed.
      }
    }

    private void RenameDesignFolderInFiles(InstallSettings installSettings)
    {
      var paths = new[]
      {
        @"Templates\Maps\javascripts\Maps.js",
        string.Format(@"Templates\Designs\{0}\js\00_libraries\15_rizzo.js", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\Maps\javascripts\Maps.js", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\Rizzo\Includes\Map.cshtml", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\05_custom\10_dataManagement.js", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\UserManagement\Login\PasswordRecoveryEmail.html", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\Includes\razor\GetImage.cshtml", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\10_custom\15_backgrounds.less", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\15_font-awesome.min.css", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\16_rizzo.css", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\20_jquery.bxslider.css", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\25_jquery.fancybox.css", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\30_jquery-ui.css ", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\05_libraries\35_jquery-ui.theme.css ", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\15_backgrounds.less", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\css\configs.less", DesignName.Text),
        string.Format(@"Templates\Designs\{0}\js\05_custom\10_dataManagement.js", DesignName.Text)
      };

      foreach (var path in paths)
      {
        ReplaceDesignNameInFile(Path.Combine(installSettings.TargetFilesFolder, path));
      }
    }

    private void ReplaceDesignNameInFile(string fileName)
    {
      if (File.Exists(fileName))
      {
        string content = File.ReadAllText(fileName);
        foreach (string designNameFolder in _designNameFolders)
        {
          if (content.Contains(designNameFolder))
          {
            content = content.Replace(designNameFolder, DesignName.Text);
            File.WriteAllText(fileName, content);
          }
        }
      }
    }

    private void AssignEmailAddress(ISettings settings, string email)
    {
      var doc = new XmlDocument();
      doc.Load(settings.TargetFilesFolder + @"\GlobalSettings.aspx");
      var node = doc.SelectSingleNode("Globalsettings/Settings/CommonInformation/Email");
      if (node == null)
      {
        MessageBox.Show($"Node Globalsettings/Settings/CommonInformation/Email not found in GlobalSettings file.");
        return;
      }
      node.InnerText = email;
      doc.Save(settings.TargetFilesFolder + @"\GlobalSettings.aspx");
    }

    private void RegisterDesignInDatabase(InstallSettings installSettings)
    {
      RegisterDesignForTable(installSettings, "");
      RegisterDesignForTable(installSettings, "Tablet");
      RegisterDesignForTable(installSettings, "Phone");
    }

    private void RegisterDesignForTable(InstallSettings installSettings, string tableSuffix)
    {
      const string sql = "UPDATE Area SET AreaLayout{0} = REPLACE(AreaLayout{0}, '/{2}/', '/{1}/')";
      foreach (var designName in _designNameFolders)
      {
        string sqlReplace = string.Format(sql, tableSuffix, DesignName.Text, designName);
        string test = string.Format(@"-S{0} -E -d""{1}"" -Q""{2}""", installSettings.SqlServerInstance, installSettings.DatabaseName, sqlReplace);
        Helpers.RunProcess("SQLCMD", test);
      }
    }

    private void RenameDesignFolders(InstallSettings installSettings)
    {
      foreach (var designName in _designNameFolders)
      {
        RenameDesignFolder(installSettings.TargetFilesFolder, @"Templates\Designs\" + designName, @"Templates\Designs\" + DesignName.Text);
        RenameDesignFolder(installSettings.TargetFilesFolder, @"Files\" + designName, @"Files\" + DesignName.Text);
        RenameDesignFolder(installSettings.TargetFilesFolder, @"Images\" + designName, @"Images\" + DesignName.Text);
      }
    }

    private void RenameDesignFolder(string root, string relativeSource, string relativeTarget)
    {
      string source = Path.Combine(root, relativeSource);
      string target = Path.Combine(root, relativeTarget);
      if (Directory.Exists(source))
      {
        Directory.Move(source, target);
      }
    }

    private void SaveItemTypeFile(ItemTreeNode rootNode)
    {
      string fileName = Path.Combine(InstallSettings.TargetFilesFolder + "\\System\\Items", rootNode.Name);
      string itemXml = File.ReadAllText(fileName);
      var doc = new XmlDocument();
      doc.LoadXml(itemXml);

      var selectSingleNode = doc.SelectSingleNode("items/item");
      var isInherited = !string.IsNullOrWhiteSpace(selectSingleNode?.Attributes?["inherits"].InnerText);

      var fileIsDirty = false;
      var xmlFields = doc.SelectNodes("items/item/fields/field");
      if (xmlFields != null)
      {
        foreach (XmlNode xmlFieldNode in xmlFields)
        {
          if (xmlFieldNode.Attributes != null)
          {
            var systemName = xmlFieldNode.Attributes["systemName"].InnerText;
            var isSelected = false;

            foreach (TreeNode childNode in rootNode.Nodes)
            {
              if (childNode is GroupTreeNode)
              {
                foreach (FieldTreeNode fieldInCategoryNode in childNode.Nodes)
                {
                  if (fieldInCategoryNode.Name == systemName)
                  {
                    isSelected = childNode.Checked;
                    break;
                  }
                }
              }
              else
              {
                if (childNode.Name == systemName)
                {
                  isSelected = childNode.Checked;
                }
              }
              if (isSelected)
              {
                break;
              }
            }
            if (!isSelected)
            {
              fileIsDirty = true;
              var fields = xmlFieldNode.ParentNode;
              fields?.RemoveChild(xmlFieldNode);

              if (!isInherited)
              {
                var layoutNodes = doc.SelectNodes("items/item/layout/groups/group/fields/field");

                if (layoutNodes != null)
                {
                  foreach (XmlNode layoutNode in layoutNodes)
                  {
                    if (layoutNode.Attributes != null && layoutNode.Attributes["systemName"].InnerText == systemName)
                    {
                      var layoutFields = layoutNode.ParentNode;
                      layoutFields?.RemoveChild(layoutNode);
                    }
                  }
                }
              }
              var groups = doc.SelectNodes("items/item/layout/groups/group");
              if (groups != null)
              {
                foreach (XmlNode groupNode in groups)
                {
                  var groupFieldNode = groupNode.SelectSingleNode("fields");
                  if (groupFieldNode != null && !groupFieldNode.HasChildNodes)
                  {
                    var groupsNode = groupNode.ParentNode;
                    groupsNode?.RemoveChild(groupNode);
                  }
                }
              }
              DeleteColumn(systemName, rootNode.Name);
            }
          }
        }
      }
      if (fileIsDirty)
      {
        File.WriteAllText(fileName, doc.InnerXml);
      }
    }

    private void DeleteColumn(string columnName, string name)
    {
      string tableName = new Regex(".xml", RegexOptions.IgnoreCase).Replace(name, "");
      string deleteColumn = string.Format(@"-S{1} -E -d""{0}"" -Q""ALTER TABLE dbo.{2} DROP COLUMN {3}""", InstallSettings.DatabaseName, InstallSettings.SqlServerInstance, tableName, columnName);
      Helpers.RunProcess("SQLCMD", deleteColumn);
    }

    private void Close_Click(object sender, EventArgs e)
    {
      if (ValidateSettings())
      {
        Hide();
      }
    }

    private bool ValidateSettings()
    {
      if (string.IsNullOrWhiteSpace(DesignName.Text))
      {
        MessageBox.Show("Design name is required.");
        return false;
      }
      var onlyLettersAndNumbersExpression = new Regex(@"^\w+$");
      var isValid = onlyLettersAndNumbersExpression.IsMatch(DesignName.Text);

      if (!isValid)
      {
        MessageBox.Show("Design name can only contain letters and numbers.");
      }
      return isValid;
    }

    private void SelectAll_Click(object sender, EventArgs e)
    {
      SetChecked(true);
    }

    private void SelectNone_Click(object sender, EventArgs e)
    {
      SetChecked(false);
    }

    private void SetChecked(bool check)
    {
      foreach (TreeNode node in treeView1.Nodes)
      {
        node.Checked = check;
        var itemNode = node as ItemTreeNode;
        if (itemNode != null)
        {
          foreach (var categoryNode in itemNode.GetGroups())
          {
            itemNode.Checked = check;
            foreach (var fieldNode in categoryNode.GetFields())
            {
              fieldNode.Checked = check;
            }
          }
        }
      }
    }

    private void ExpandAll_Click(object sender, EventArgs e)
    {
      treeView1.ExpandAll();
    }

    private void CollapseAll_Click(object sender, EventArgs e)
    {
      treeView1.CollapseAll();
    }

    private void ExpandItems_Click(object sender, EventArgs e)
    {
      treeView1.CollapseAll();
      foreach (TreeNode node in treeView1.Nodes)
      {
        node.Expand();
      }
    }

    internal void ConfigureMiscellaneous(InstallSettings installSettings)
    {
      string email = Email.Text;
      if (!string.IsNullOrWhiteSpace(email))
      {
        AssignEmailAddress(installSettings, email);
      }
    }

    private void rdCleanInstall_CheckedChanged(object sender, EventArgs e)
    {
      if (rdCleanInstall.Checked)
      {
        cleanFolders.Checked = true;
        clearOrderHistory.Checked = true;
        clearStats.Checked = true;
        clearModuleData.Checked = true;
        EnableDisableOptions(false);
      }
      else
      {
        EnableDisableOptions(true);
      }
    }

    private void EnableDisableOptions(bool enable)
    {
      cleanFolders.Enabled = enable;
      clearOrderHistory.Enabled = enable;
      clearStats.Enabled = enable;
      clearModuleData.Enabled = enable;
    }
  }
}
